#-*-coding:utf-8-*-
# Create your views here.
from crm.models import Role
import role_cfg as rcfg
from crm.models import Agent
from crm.models import Furtherinterest
from crm.models import Projects
from crmv2.models import Student
from models import Follow_up,Next_Follow_Date
from crm.models import Sales_record
#from crm.models import Maillist,Maillog
from django.shortcuts import get_object_or_404,render_to_response
from django.http import Http404, HttpResponse, HttpResponseRedirect
#from django import forms
from django.core.paginator import Paginator, InvalidPage
import time,datetime,check,random
from django.db import connection 
import logClass as log

log = log.Log()

def afterWeekDate():
    today = datetime.datetime.today()
    aWeekDelta = datetime.timedelta(weeks=1)
    aWeekAgo = today - aWeekDelta
    return datetime.datetime.strftime(aWeekAgo,'%Y-%m-%d')

"""查看roles列表清单"""
def invoice (request):
    '''发送邮件页面和处理ajax发送邮件'''
    if not check.checklogin(request):
        return HttpResponseRedirect('/')

    is_con = int(request.GET.get('is_con',0))
    begin = request.GET.get('starttime',None)
    end = request.GET.get('endtime',0)
    stu_name_id = request.GET.get('stu_name_id',0)
    p_id = int(request.GET.get('p_id',request.session['program']))
    descript = request.GET.get('descript','datefee')
    amount = descript

    try:
        project_list = Projects.objects.order_by('name')
    except Exception,e:
        log.error(e)
    where = 'where 1=1'
    error_info = ''
    names = ''
    money_ret = ''

    if not begin:
        begin = afterWeekDate()

    if not end:
        end = datetime.datetime.strftime(datetime.datetime.today(),'%Y-%m-%d')

    if end<begin:
        error_info = 'Start date is not greater than end date'
    
    if error_info:
        pass;
    else:
        try:
            cursor = connection.cursor()
            name_sql = '''select b.`id`,b.`fullname`,b.`name_en` from program_student_course a join crmv2_student b on b.id=a.student_id where a.project_id=%s and a.class_start>="%s" and a.class_start <="%s" group by b.fullname,b.`name_en`'''%(p_id,begin,end)
            cursor.execute(name_sql) 
            name_list = cursor.fetchall()
            names = []
            temp = []
            for name in name_list:
                temp = list(name)
                names.append(temp)
        
            if not stu_name_id and names:
                stu_name_id = names[0][0]
            
            if descript=='amount':
                descript = 'date1'

            where += ''' and `student_id`=%s and `project_id`=%s'''%(stu_name_id,p_id)
            invo_sql = '''select `moneytype%s`,`%s_money`,`datefee`,`datefee_paid`,`datereg`,`datereg_paid`,`date1`,`date1_paid`,`date2`,`date2_paid`,`date3`,`date3_paid`,`date4`,`date4_paid`,`moneytype`,`amount` from `student_payment_record` %s'''%(descript,descript,where)
            cursor.execute(invo_sql) 
            money_ret = cursor.fetchall()
            money_ret = money_ret[0]
        except Exception,e:
            log.error(`e`)
    begy,begm,begd = time.strptime(begin,'%Y-%m-%d')[0:3]
    endy,endm,endd = time.strptime(end,'%Y-%m-%d')[0:3]
    begtime = datetime.datetime(begy,begm,begd)
    endtime = datetime.datetime(endy,endm,endd)
    return render_to_response('report/account/invoice.html',{
        'begin':begtime,
        'end':endtime,
        'today':datetime.datetime.today(),
        'project_list':project_list,
        'is_con':is_con,
        'stu_name_id':int(stu_name_id),
        'stu_names':names,
        'p_id':p_id,
        'amount':amount,
        'money_ret':money_ret,
        'random':random.randint(100,1000),
        'ttt':''
    })

def receipt (request):
    '''发送邮件页面和处理ajax发送邮件'''
    if not check.checklogin(request):
        return HttpResponseRedirect('/')

    is_con = int(request.GET.get('is_con',0))
    begin = request.GET.get('starttime',None)
    end = request.GET.get('endtime',0)
    stu_name_id = request.GET.get('stu_name_id',0)
    p_id = int(request.GET.get('p_id',request.session['program']))
    descript = request.GET.get('descript','datefee')
    amount = descript

    try:
        project_list = Projects.objects.order_by('name')
    except Exception,e:
        log.error(e)
    where = 'where 1=1'
    error_info = ''
    names = ''
    money_ret = ''
    invo_sql=''

    if not begin:
        begin = afterWeekDate()

    if not end:
        end = datetime.datetime.strftime(datetime.datetime.today(),'%Y-%m-%d')

    if end<begin:
        error_info = 'Start date is not greater than end date'
    
    if error_info:
        pass;
    else:
        try:
            cursor = connection.cursor()
            name_sql = '''select b.`id`,b.`fullname`,b.`name_en` from program_student_course a join crmv2_student b on b.id=a.student_id where a.project_id=%s and a.class_start>="%s" and a.class_start <="%s" group by b.fullname,b.`name_en`'''%(p_id,begin,end)
            cursor.execute(name_sql) 
            name_list = cursor.fetchall()
            names = []
            temp = []
            for name in name_list:
                temp = list(name)
                names.append(temp)
        
            if not stu_name_id and names:
                stu_name_id = names[0][0]
            
            if descript=='amount':
                descript = 'date1'

            where += ''' and `student_id`=%s and `project_id`=%s'''%(stu_name_id,p_id)
            invo_sql = '''select `moneytype%s`,`%s_money`,`%s_paiy_by`,`%s_handed_by`,`moneytype`,`amount` from `student_payment_record` %s'''%(descript,descript,descript,descript,where)
            cursor.execute(invo_sql) 
            money_ret = cursor.fetchall()
            money_ret = money_ret[0]
        except Exception,e:
            log.error(e)
    begy,begm,begd = time.strptime(begin,'%Y-%m-%d')[0:3]
    endy,endm,endd = time.strptime(end,'%Y-%m-%d')[0:3]
    begtime = datetime.datetime(begy,begm,begd)
    endtime = datetime.datetime(endy,endm,endd)
    return render_to_response('report/account/receipt.html',{
        'begin':begtime,
        'end':endtime,
        'today':datetime.datetime.today(),
        'project_list':project_list,
        'is_con':is_con,
        'stu_name_id':int(stu_name_id),
        'stu_names':names,
        'p_id':p_id,
        'amount':amount,
        'money_ret':money_ret,
        'random':random.randint(100,1000),
        'ttt':''
    })

def payment (request):
    '''发送邮件页面和处理ajax发送邮件'''
    if not check.checklogin(request):
        return HttpResponseRedirect('/')

    is_con = int(request.GET.get('is_con',0))
    stu_name_id = request.GET.get('stu_name_id',0)
    p_id = int(request.GET.get('p_id',request.session['program']))
    descript = request.GET.get('descript','datefee')
    amount = descript

    try:
        project_list = Projects.objects.order_by('name')
    except Exception,e:
        log.error(e)
    where = 'where 1=1'
    error_info = ''
    names = ''
    money_ret = ''
    invo_sql=''
    amount_money= {}

    if error_info:
        pass;
    else:
        try:
            cursor = connection.cursor()
            name_sql = '''select b.`id`,b.`fullname`,b.`name_en` from program_student_course a join crmv2_student b on b.id=a.student_id where a.project_id=%s group by b.fullname,b.`name_en`'''%(p_id)
            cursor.execute(name_sql) 
            name_list = cursor.fetchall()
            names = []
            temp = []
            for name in name_list:
                temp = list(name)
                names.append(temp)
        
            if not stu_name_id and names:
                stu_name_id = names[0][0]
            
            if descript=='amount':
                descript = 'date1'

            where += ''' and `student_id`=%s and `project_id`=%s'''%(stu_name_id,p_id)
            invo_sql = '''select `moneytype%s`,`%s_money`,`%s_paiy_by`,`%s_handed_by`,`moneytype`,`amount`,`date1`,`date1_money`,`moneytypedate1`,`date2`,`date2_money`,`moneytypedate2`,`date3`,`date3_money`,`moneytypedate3`,`date4`,`date4_money`,`moneytypedate4` from `student_payment_record` %s'''%(descript,descript,descript,descript,where)
            cursor.execute(invo_sql) 
            money_ret = cursor.fetchall()
            if len(money_ret)>0:
                money_ret = money_ret[0]
                if amount_money.has_key(money_ret[8]):
                    amount_money[money_ret[8]] += int(money_ret[7])
                else:
                    amount_money[money_ret[8]] = int(money_ret[7])
                
                if amount_money.has_key(money_ret[11]):
                    amount_money[money_ret[11]] += int(money_ret[10])
                else:
                    amount_money[money_ret[11]] = int(money_ret[10])
                
                if amount_money.has_key(money_ret[14]):
                    amount_money[money_ret[14]] += int(money_ret[13])
                else:
                    amount_money[money_ret[14]] = int(money_ret[13])
                
                if amount_money.has_key(money_ret[17]):
                    amount_money[money_ret[17]] += int(money_ret[16])
                else:
                    amount_money[money_ret[17]] = int(money_ret[16])
              
        except Exception,e:
            log.error(e)
        amount_money = `amount_money`.replace('u','')[1:-1]
    return render_to_response('report/account/payment.html',{
        'today':datetime.datetime.today(),
        'project_list':project_list,
        'is_con':is_con,
        'stu_name_id':int(stu_name_id),
        'stu_names':names,
        'amount_money':amount_money,
        'p_id':p_id,
        'amount':amount,
        'money_ret':money_ret,
        'random':random.randint(100,1000),
        'ttt':''
    })

def overdue (request):
    '''发送邮件页面和处理ajax发送邮件'''
    if not check.checklogin(request):
        return HttpResponseRedirect('/')

    is_con = int(request.GET.get('is_con',0))
    begin = request.GET.get('starttime',None)
    end = request.GET.get('endtime',0)
    stu_name_id = request.GET.get('stu_name_id',0)
    p_id = int(request.GET.get('p_id','0'))
    descript = request.GET.get('descript','datefee_money')
    amount = descript

    try:
        project_list = Projects.objects.order_by('name')
    except Exception,e:
        log.error(e)
    where = 'where 1=1'
    error_info = ''
    payment_ret = ''

    if not begin:
        begin = afterWeekDate()

    if not end:
        end = datetime.datetime.strftime(datetime.datetime.today(),'%Y-%m-%d')

    if end<begin:
        error_info = 'Start date is not greater than end date'
    
    if error_info:
        pass;
    else:
        try:
            projects = {}
            for project in project_list:
                projects[project.id]=project.name

            cursor = connection.cursor()
            where += ''' and a.`date1`>="%s" and a.`date1`<="%s" '''%(begin,end)

            if p_id:
                where += ''' and a.project_id=%s'''%p_id

            payment_sql = '''select a.`project_id`,b.fullname,b.name_en,b.name_ch,b.email,b.email2,a.date1,a.date1_money,a.date1_paid,a.date2,a.date2_money,a.date2_paid,a.date3,a.date3_money,a.date3_paid,a.date4,a.date4_money,a.date4_paid,b.remark from `student_payment_record` a join `crmv2_student` b on b.id=a.student_id %s and a.student_id=b.id group by a.student_id'''%where
            cursor.execute(payment_sql) 
            pay_ret = cursor.fetchall()
            payment_ret=[]
            temp= []
            for ret in pay_ret:
                temp = list(ret)
                temp[0] = projects[ret[0]]
                payment_ret.append(temp)
                temp = []
                
        except Exception,e:
            log.error(e)
    begy,begm,begd = time.strptime(begin,'%Y-%m-%d')[0:3]
    endy,endm,endd = time.strptime(end,'%Y-%m-%d')[0:3]
    begtime = datetime.datetime(begy,begm,begd)
    endtime = datetime.datetime(endy,endm,endd)
    return render_to_response('report/account/overdue.html',{
        'begin':begtime,
        'end':endtime,
        'today':datetime.datetime.today(),
        'project_list':project_list,
        'is_con':is_con,
        'p_id':p_id,
        'payment_ret':payment_ret,
        'ttt':''
    })

def report_export(request):    
    date = request.POST.get('d','')
    title = request.POST.get('t','')

    if not date or not title:
        return HttpResponse('''参数错误''')

    out_str = '''%s'''%(date)

    from django.template import loader, RequestContext
    response = HttpResponse(mimetype='application/ms-excel')
    response['Content-Disposition'] = 'attachment; filename=%s.xls' %title
    t = loader.get_template('report/export.html')
    c = RequestContext(request, {
        'date':out_str
    })
    response.write(t.render(c))
    return response
